package com.davidhigenbottam.HeatMap.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;

import com.davidhigenbottam.HeatMap.shared.Institution;
import com.davidhigenbottam.HeatMap.shared.Province;
import com.davidhigenbottam.HeatMap.shared.Query;


public class HigherEducationEnrolementProvinceData extends ProvinceData {

	public HigherEducationEnrolementProvinceData(Query query) {
		super(query);
	}

	@Override
	public String getDescription() {
		return "HE Enrolment";
	}



	@Override
	public void getProvincesFromDatabase() {
		Connection c = null;
		try {
			c = getConnection();
			getAllProvinces(c);

			Boolean filterLevelEducation= !query.getLevelOfEducation().equals(Query.STUDY_LEVEL_ALL);
			String levelEducationClause="";
			if(filterLevelEducation){
				//filter on level of education
				levelEducationClause = "and student_group.study_id=? ";
			}			
			
			Boolean filterInstType= !query.getInstitutionType().equals(Query.INSTITUTE_TYPE_ALL);
			String institutionTypeClause="";
			if(filterInstType){
				//filter on institute type
				institutionTypeClause = "and student_group.inst_form_id=? ";
			}
			
			Boolean filterInstOwnership= !query.getInstituteOwnership().equals(Query.INSTITUTE_OWNERSHIP_ALL);
			String instituteOwnershipClause="";
			if(filterInstOwnership){
				//filter on institute type
				instituteOwnershipClause = "and student_group.type_inst=? ";
			}
			
			
			String statement = "select province.id,province.name,prov_name, sum(total) as sumtotal  "
					+ "from student_group, province "
					+ "where province.student_group_id = student_group.prov_id "
					+ "and province.country_id=? "
					+ "and student_group.year=? "
					+ levelEducationClause
					+ institutionTypeClause
					+ instituteOwnershipClause
					+ "group by province.id " 
					+ "order by sumtotal desc";
			log.info("statement:"+statement);
			
			PreparedStatement stmt = c.prepareStatement(statement);
			int idx=1;
			stmt.setString(idx++, query.getCountryCode());
			stmt.setString(idx++, query.getYear());
			if(filterLevelEducation)
				stmt.setString(idx++, query.getLevelOfEducation());
			if(filterInstType)
				stmt.setString(idx++, query.getInstitutionType());
			if(filterInstOwnership)
				stmt.setString(idx++, query.getInstituteOwnership());
			
			if (stmt.execute()) {
				ResultSet rs = stmt.getResultSet();
				while (rs.next()) {
					String prov = rs.getString("name");
					Long sumTotal = rs.getLong("sumtotal");
					Province p = provinces.get(prov);
					p.setVal(Double.valueOf(sumTotal.doubleValue()));
					//log.info("prov:"+prov+" sumTotal:"+sumTotal);
				}
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, "SQLException", e);
			throw new RuntimeException(e);
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
		}
		
	}

	@Override
	public void getInstitutionsFromDatabase(Province p) {
		Connection c = null;
		try {
			c = getConnection();

			Boolean filterLevelEducation= !query.getLevelOfEducation().equals(Query.STUDY_LEVEL_ALL);
			String levelEducationClause="";
			if(filterLevelEducation){
				//filter on level of education
				levelEducationClause = "and student_group.study_id=? ";
			}			
			
			Boolean filterInstType= !query.getInstitutionType().equals(Query.INSTITUTE_TYPE_ALL);
			String institutionTypeClause="";
			if(filterInstType){
				//filter on institute type
				institutionTypeClause = "and student_group.inst_form_id=? ";
			}
			
			Boolean filterInstOwnership= !query.getInstituteOwnership().equals(Query.INSTITUTE_OWNERSHIP_ALL);
			String instituteOwnershipClause="";
			if(filterInstOwnership){
				//filter on institute type
				instituteOwnershipClause = "and student_group.type_inst=? ";
			}
			

			
			String statement = "select inst_id as id, inst_name as name, sum(total) as sumtotal "
					+ "from student_group, province "
					+ "where province.student_group_id = student_group.prov_id "
					+ "and province.id=? "
					+ "and student_group.year=? "
					+ levelEducationClause
					+ institutionTypeClause
					+ instituteOwnershipClause
					+ "group by inst_id";
			log.info("statement:"+statement);
			
			PreparedStatement stmt = c.prepareStatement(statement);
			int idx=1;
			stmt.setLong(idx++, p.getId());
			stmt.setString(idx++, query.getYear());
			if(filterLevelEducation)
				stmt.setString(idx++, query.getLevelOfEducation());
			if(filterInstType)
				stmt.setString(idx++, query.getInstitutionType());
			if(filterInstOwnership)
				stmt.setString(idx++, query.getInstituteOwnership());
			
			if (stmt.execute()) {
				ResultSet rs = stmt.getResultSet();
				while (rs.next()) {
					//Long id = rs.getLong("id");
					String inst = rs.getString("name");
					
					Long sumTotal = rs.getLong("sumtotal");
					
					Institution i= new Institution(inst,sumTotal);
					p.addInstitution(i);

					//p.setVal(Double.valueOf(sumTotal.doubleValue()));
					//log.info("prov:"+prov+" sumTotal:"+sumTotal);
				}
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, "SQLException", e);
			throw new RuntimeException(e);
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
		}
	}

	

}
